/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Taxi.db;

import Taxi.model.Employee;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author U24022
 */
public class EmployeeDAO {

    public int save(Employee employee) throws SQLException {
        System.out.println("INSERT");
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        return statement.executeUpdate("INSERT INTO employee VALUES( "
                  + employee.getEmpNum()  + ", " + "'"
                  + employee.getFirstName()  + "' , " + "'"
                  + employee.getLastName()  + "' , " + "'"
                  + employee.getAddress() + "' , " + "'"
                  + employee.getBday() + "' , " + "'"
                  + employee.getLicense() + "', " +
                  + employee.getSss() + " , " + 
                  + employee.getPagibig() + " , " + 
                  + employee.getPhilHealth() + " , " + "'"
                  + employee.getStartDate() + "' , " + "'"
                  + employee.getEndDate() + "')");
    }

     public int update(Employee employee) throws SQLException {
         System.out.println("UPDATE");
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        return statement.executeUpdate("UPDATE EMPLOYEE SET emp_num="
                  + employee.getEmpNum()+  ",first_name='"
                  + employee.getFirstName()  + "', last_name='"
                  + employee.getLastName()  + "', address='"
                  + employee.getAddress() + "', birthday='"
                  + employee.getBday() + "',license='"
                  + employee.getLicense() + "',sss="
                  + employee.getSss() + ",pagibig="
                  + employee.getPagibig() + ",philhealth="
                  + employee.getPhilHealth() + ",start_date='"
                  + employee.getStartDate() + "',end_date='"
                  + employee.getEndDate() + "' WHERE first_name='"
                  + employee.getFirstName() + "'");
             }

// sample UPDATE employee SET emp_num =2 WHERE emp_num =1 AND first_name ='Chan' AND last_name='Howard' AND address='129378 askhdao 1' AND birthday='2000-10-20' AND license='qiweju123734';

     public Employee getEmployeeByFirstName(String firstname) throws SQLException{
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM EMPLOYEE WHERE first_name='"
                + firstname + "'");
        Employee employee = new Employee();
        rs.next();
        employee.setEmpNum(rs.getInt("emp_num"));
        employee.setFirstName(rs.getString("first_name"));
        employee.setLastName(rs.getString("last_name"));
        employee.setAddress(rs.getString("address"));
        employee.setBday(rs.getString("birthday"));
        employee.setLicense(rs.getString("license"));
        employee.setSss(rs.getInt("sss"));
        employee.setPagibig(rs.getInt("pagibig"));
        employee.setPhilHealth(rs.getInt("philhealth"));
        employee.setStartDate(rs.getString("start_date"));
        employee.setEndDate(rs.getString("end_date"));
        return employee;
    }

    public void deleteByFirstName(String firstname) {
         try {
           Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
          int i = statement
              .executeUpdate("DELETE FROM EMPLOYEE WHERE first_name = '"
                  + firstname + "'");
        } catch (SQLException insertException) {
           insertException.printStackTrace();
        }
    }

    public List<Employee> getAllEmployee() throws SQLException{
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM Employee");
        List<Employee> employees = new LinkedList<Employee>();
        while (rs.next()) {
            Employee employee = new Employee();
            employee.setEmpNum(rs.getInt("emp_num"));
            employee.setFirstName(rs.getString("first_name"));
            employee.setLastName(rs.getString("last_name"));
            employee.setAddress(rs.getString("address"));
            employee.setBday(rs.getString("birthday"));
            employee.setLicense(rs.getString("license"));
            employee.setSss(rs.getInt("sss"));
            employee.setPagibig(rs.getInt("pagibig"));
            employee.setPhilHealth(rs.getInt("philhealth"));
            employee.setStartDate(rs.getString("start_date"));
            employee.setEndDate(rs.getString("end_date"));
            employees.add(employee);
        }
        return employees;
    }

}
